/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package da;

import entities.Branch;
import entities.LoanType;
import entities.User;
import entities.UserType;
import java.sql.Date;
import java.util.ArrayList;
import javax.swing.JOptionPane;

/**
 *
 * @author HungVM
 */
public class DAUser {

    MSSQLConnection conn =null;

    public DAUser() {
        conn = new MSSQLConnection();
    }

        //Ham checkLogin
    public boolean checkLogin(User user){
        try {
            //truy xuat co so dung lieu va kiểm tra
            //MSSQLConnection conn = new MSSQLConnection("PFS-HUNGVM4", "sa", "789123", "PhongKhamDB", 1433);
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{call sp_checkLogin(?,?)}");
            conn.cstm.setString(1, user.getUserName());
            conn.cstm.setString(2, user.getPassWord());
            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                UserType type = new UserType();
                type.setTypeId(conn.rs.getInt("TypeId"));
                type.setTypeName(conn.rs.getString("TypeName"));
                Branch branch = new Branch();
                branch.setBranchId(conn.rs.getInt("BranchId"));
                branch.setBranchName(conn.rs.getString("BranchName"));
                branch.setAddress(conn.rs.getString("Address"));
                branch.setDescription(conn.rs.getString("Description"));
                branch.setPhoneNumber(conn.rs.getString("PhoneNumber"));
                
                user.setUserId(conn.rs.getInt("UserId"));
                user.setUserName(conn.rs.getString("UserName"));
                user.setPassWord(conn.rs.getString("password"));
                user.setFullName(conn.rs.getString("FullName"));
                user.setAddress(conn.rs.getString("address"));
                user.setBirthDay(conn.rs.getDate("Birthday"));
                user.setEmail(conn.rs.getString("Email"));
                user.setIdentity(conn.rs.getString("Identity"));
                user.setPhone(conn.rs.getString("Phone"));
                user.setSex(conn.rs.getBoolean("sex"));
                //user.getUserType().setTypeId(conn.rs.getInt(11));
                //user.getBranchId().setBranchId(conn.rs.getInt(12));
                user.setIsLock(conn.rs.getBoolean("isLock"));
                user.setStatus(conn.rs.getBoolean("status"));
                user.setNote(conn.rs.getString("note"));
                user.setUserType(type);
                user.setBranchId(branch);
                conn.myClose();
                return true;
            }
            conn.myClose();
            return false;
        } catch (Exception ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(null, ex.getMessage());
            return false;
        }
    }

    public boolean changePass_DAL(String user, String old,String newPass, String newPassConfirm){
//        MSSQLConnection conn = new MSSQLConnection(ServerConfig.txtServerName.getText(),
//                ServerConfig.txtUser.getText(),
//                new String (ServerConfig.txtPass.getPassword()),
//                ServerConfig.txtDataName.getText(),
//                Integer.parseInt(ServerConfig.txtPort.getText()));
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{call sp_ChangePass(?,?,?)}");
            conn.cstm.setString(1, user);
            conn.cstm.setString(2, old);
            conn.cstm.setString(3, newPass);
            int i = conn.cstm.executeUpdate();
            conn.myClose();
            if(i>0){
                return true;
            }
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null, ex.getMessage());
        }
        return false;

    }

    public ArrayList<User> getAll(){
        ArrayList<User> list = new ArrayList<User>();
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{call sp_getUserAll}");
            conn.rs = conn.cstm.executeQuery();
            Branch br;
            UserType uType;
            while(conn.rs.next()){
                br =new Branch();
                uType = new UserType();
                User user  =new User();
                user.setUserId(conn.rs.getInt(1));
                user.setUserName(conn.rs.getString(2));
                user.setPassWord(conn.rs.getString(3));
                user.setFullName(conn.rs.getString(4));
                user.setAddress(conn.rs.getString(5));
                user.setBirthDay(conn.rs.getDate(6));
                user.setEmail(conn.rs.getString(7));
                user.setIdentity(conn.rs.getString(8));
                user.setPhone(conn.rs.getString(9));
                user.setSex(conn.rs.getBoolean(10));
                //user.setViTri(conn.rs.getString(12));
                user.setIsLock(conn.rs.getBoolean(13));
                user.setStatus(conn.rs.getBoolean(14));
                user.setNote(conn.rs.getString(15));
                br.setBranchId(conn.rs.getInt(11));
                br.setBranchName(conn.rs.getString(17));
                uType.setTypeId(conn.rs.getInt(12));
                uType.setTypeName(conn.rs.getString(16));
                
                user.setBranchId(br);
                user.setUserType(uType);
                list.add(user);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, " " + e.getMessage(), "Lỗi", JOptionPane.ERROR);
        }
        return list;
    }
    
    public boolean insertUser(User user){
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{call sp_InsertUser(?,?,?,?,?,?,?,?,?,?,?)}");
            conn.cstm.setString(1, user.getUserName());
            conn.cstm.setString(2, user.getFullName());
            conn.cstm.setString(3, user.getAddress());
            conn.cstm.setDate(4, user.getBirthDay());
            conn.cstm.setString(5, user.getEmail());
            conn.cstm.setString(6, user.getIdentity());
            conn.cstm.setString(7, user.getPhone());
            conn.cstm.setBoolean(8, user.isSex());
            conn.cstm.setInt(9, user.getUserType().getTypeId());
            conn.cstm.setInt(10, user.getBranchId().getBranchId());
            conn.cstm.setString(11, user.getNote());
            
            int n= conn.cstm.executeUpdate();
            conn.myClose();
            if(n==0)
                return false;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return true;

    }

    public boolean updateUser(User user){
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{call sp_updateUser(?,?,?,?,?,?,?,?,?,?,?,?)}");
            conn.cstm.setInt(1, user.getUserId());
            conn.cstm.setString(2, user.getUserName());
            conn.cstm.setString(3, user.getFullName());
            conn.cstm.setString(4, user.getAddress());
            conn.cstm.setDate(5,user.getBirthDay());
            conn.cstm.setString(6, user.getEmail());
            conn.cstm.setString(7, user.getIdentity());
            conn.cstm.setString(8, user.getPhone());
            conn.cstm.setBoolean(9, user.isSex());
            conn.cstm.setInt(10, user.getUserType().getTypeId());
            conn.cstm.setInt(11, user.getBranchId().getBranchId());
            conn.cstm.setString(12, user.getNote());
            int n= conn.cstm.executeUpdate();
            conn.myClose();
            if(n>0)
                return true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }
    
    public ArrayList<User> getByFullName(String fName){
        ArrayList<User> list = new ArrayList<User>();
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{call sp_getUserByFullName(?)}");
            conn.cstm.setString(1, fName);
            conn.rs = conn.cstm.executeQuery();
            User user;
            UserType type;
            Branch br;
            while(conn.rs.next()){
                user = new User();
                type =new UserType();
                br  =new Branch();

                user.setUserId(conn.rs.getInt("userID"));
                user.setUserName(conn.rs.getString("UserName"));
                user.setPassWord(conn.rs.getString("PassWord"));
                user.setFullName(conn.rs.getString("FullName"));
                user.setAddress(conn.rs.getString("Address"));
                user.setBirthDay(conn.rs.getDate("BirthDay"));
                user.setEmail(conn.rs.getString("Email"));
                user.setIdentity(conn.rs.getString("Identity"));
                user.setPhone(conn.rs.getString("Phone"));
                user.setSex(conn.rs.getBoolean("sex"));
                
                type.setTypeId(conn.rs.getInt("typeId"));
                type.setTypeName(conn.rs.getString("TypeName"));
                br.setBranchId(conn.rs.getInt("BranchId"));
                user.setNote(conn.rs.getString("Note"));
                
                //nv.setQuyen(null);
                user.setIsLock(conn.rs.getBoolean("isLock"));
                br.setBranchName(conn.rs.getString("BranchName"));
                br.setPhoneNumber(conn.rs.getString("PhoneNumber"));
                br.setDescription(conn.rs.getString("Description"));
                br.setAddress(conn.rs.getString("BAddress"));
                br.setStatus(conn.rs.getBoolean("BStatus"));

                user.setUserType(type);
                user.setBranchId(br);
                list.add(user);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, e.getMessage(), "Error", JOptionPane.ERROR);
        }
        return list;
    }
    
    public ArrayList<User> getByUserName(String uName){
        ArrayList<User> list = new ArrayList<User>();
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{call sp_getUserByUserName(?)}");
            conn.cstm.setString(1, uName);
            conn.rs = conn.cstm.executeQuery();
            User user;
            UserType type;
            Branch br;
            while(conn.rs.next()){
                user = new User();
                type =new UserType();
                br  =new Branch();

                user.setUserId(conn.rs.getInt("userID"));
                user.setUserName(conn.rs.getString("UserName"));
                user.setPassWord(conn.rs.getString("PassWord"));
                user.setFullName(conn.rs.getString("FullName"));
                user.setAddress(conn.rs.getString("Address"));
                user.setBirthDay(conn.rs.getDate("BirthDay"));
                user.setEmail(conn.rs.getString("Email"));
                user.setIdentity(conn.rs.getString("Identity"));
                user.setPhone(conn.rs.getString("Phone"));
                user.setSex(conn.rs.getBoolean("sex"));
                
                type.setTypeId(conn.rs.getInt("typeId"));
                type.setTypeName(conn.rs.getString("TypeName"));
                br.setBranchId(conn.rs.getInt("BranchId"));
                user.setNote(conn.rs.getString("Note"));
                
                //nv.setQuyen(null);
                user.setIsLock(conn.rs.getBoolean("isLock"));
                br.setBranchName(conn.rs.getString("BranchName"));
                br.setPhoneNumber(conn.rs.getString("PhoneNumber"));
                br.setDescription(conn.rs.getString("Description"));
                br.setAddress(conn.rs.getString("BAddress"));
                br.setStatus(conn.rs.getBoolean("BStatus"));

                user.setUserType(type);
                user.setBranchId(br);
                list.add(user);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "Khong the ket noi CSDL", "Lỗi", JOptionPane.ERROR);
        }
        return list;
    }
   /* Edit method getByID : 
     * INPUT    : int id;
     * OUTPUT   : ArrayList<User>
     * a viet them proc get_User_By_ID 
    * 
    */
    public ArrayList<User> getById(int id){
        ArrayList<User> list = new ArrayList<User>();
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{call sp_getUserByID(?)}");
            conn.cstm.setInt(1, id);
            conn.rs = conn.cstm.executeQuery();
            User user;
            UserType type;
            Branch br;
            while(conn.rs.next()){
                user = new User();
                type =new UserType();
                br  =new Branch();

                user.setUserId(conn.rs.getInt("userID"));
                user.setUserName(conn.rs.getString("UserName"));
                user.setPassWord(conn.rs.getString("PassWord"));
                user.setFullName(conn.rs.getString("FullName"));
                user.setAddress(conn.rs.getString("Address"));
                user.setBirthDay(conn.rs.getDate("BirthDay"));
                user.setEmail(conn.rs.getString("Email"));
                user.setIdentity(conn.rs.getString("Identity"));
                user.setPhone(conn.rs.getString("Phone"));
                user.setSex(conn.rs.getBoolean("sex"));
                
                type.setTypeId(conn.rs.getInt("typeId"));
                type.setTypeName(conn.rs.getString("TypeName"));
                br.setBranchId(conn.rs.getInt("BranchId"));
                user.setNote(conn.rs.getString("Note"));
                
                //nv.setQuyen(null);
                user.setIsLock(conn.rs.getBoolean("isLock"));
                br.setBranchName(conn.rs.getString("BranchName"));
                br.setPhoneNumber(conn.rs.getString("PhoneNumber"));
                br.setDescription(conn.rs.getString("Description"));
                br.setAddress(conn.rs.getString("BAddress"));
                br.setStatus(conn.rs.getBoolean("BStatus"));

                user.setUserType(type);
                user.setBranchId(br);
                list.add(user);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "Khong the ket noi CSDL", "Lỗi", JOptionPane.ERROR);
        }
        return list;

    }
    
     public ArrayList<User> getByIdentity(String identity){
        ArrayList<User> list = new ArrayList<User>();
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{call sp_getUserByIdentity(?)}");
            conn.cstm.setString(1, identity);
            conn.rs = conn.cstm.executeQuery();
            User user;
            UserType type;
            Branch br;
            while(conn.rs.next()){
                user = new User();
                type =new UserType();
                br  =new Branch();

                user.setUserId(conn.rs.getInt("userID"));
                user.setUserName(conn.rs.getString("UserName"));
                user.setPassWord(conn.rs.getString("PassWord"));
                user.setFullName(conn.rs.getString("FullName"));
                user.setAddress(conn.rs.getString("Address"));
                user.setBirthDay(conn.rs.getDate("BirthDay"));
                user.setEmail(conn.rs.getString("Email"));
                user.setIdentity(conn.rs.getString("Identity"));
                user.setPhone(conn.rs.getString("Phone"));
                user.setSex(conn.rs.getBoolean("sex"));
                
                type.setTypeId(conn.rs.getInt("typeId"));
                type.setTypeName(conn.rs.getString("TypeName"));
                br.setBranchId(conn.rs.getInt("BranchId"));
                user.setNote(conn.rs.getString("Note"));
                
                //nv.setQuyen(null);
                user.setIsLock(conn.rs.getBoolean("isLock"));
                br.setBranchName(conn.rs.getString("BranchName"));
                br.setPhoneNumber(conn.rs.getString("PhoneNumber"));
                br.setDescription(conn.rs.getString("Description"));
                br.setAddress(conn.rs.getString("BAddress"));
                br.setStatus(conn.rs.getBoolean("BStatus"));

                user.setUserType(type);
                user.setBranchId(br);
                list.add(user);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "Khong the ket noi CSDL", "Lỗi", JOptionPane.ERROR);
        }
        return list;

    }
     
     public ArrayList<User> getLock(int isLock){
        ArrayList<User> list = new ArrayList<User>();
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{call sp_getUserByLock(?)}");
            conn.cstm.setInt(1, isLock);
            conn.rs = conn.cstm.executeQuery();
            User user;
            UserType type;
            Branch br;
            while(conn.rs.next()){
                user = new User();
                type =new UserType();
                br  =new Branch();

                user.setUserId(conn.rs.getInt("userID"));
                user.setUserName(conn.rs.getString("UserName"));
                user.setPassWord(conn.rs.getString("PassWord"));
                user.setFullName(conn.rs.getString("FullName"));
                user.setAddress(conn.rs.getString("Address"));
                user.setBirthDay(conn.rs.getDate("BirthDay"));
                user.setEmail(conn.rs.getString("Email"));
                user.setIdentity(conn.rs.getString("Identity"));
                user.setPhone(conn.rs.getString("Phone"));
                user.setSex(conn.rs.getBoolean("sex"));
                
                type.setTypeId(conn.rs.getInt("typeId"));
                type.setTypeName(conn.rs.getString("TypeName"));
                br.setBranchId(conn.rs.getInt("BranchId"));
                user.setNote(conn.rs.getString("Note"));
                
                //nv.setQuyen(null);
                user.setIsLock(conn.rs.getBoolean("isLock"));
                br.setBranchName(conn.rs.getString("BranchName"));
                br.setPhoneNumber(conn.rs.getString("PhoneNumber"));
                br.setDescription(conn.rs.getString("Description"));
                br.setAddress(conn.rs.getString("BAddress"));
                br.setStatus(conn.rs.getBoolean("BStatus"));

                user.setUserType(type);
                user.setBranchId(br);
                list.add(user);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "Khong the ket noi CSDL", "Lỗi", JOptionPane.ERROR);
        }
        return list;

    }
     
    

    public boolean deleteUser(int id){
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{Call sp_DeleteUser(?)}");
            conn.cstm.setInt(1,id);
            int n = conn.cstm.executeUpdate();
            if(n>0)
                return true;
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean checkUser(String user){
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{Call sp_CheckUser(?)}");
            conn.cstm.setString(1, user);
            boolean f =false;
            conn.rs = conn.cstm.executeQuery();
            while(conn.rs.next()){
                f= true;
            }
            conn.myClose();
            return f;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }
    
    public boolean checkIdentity(String identity){
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{Call sp_CheckIdentity(?)}");
            conn.cstm.setString(1, identity);
            boolean f = false;
            conn.rs = conn.cstm.executeQuery();
            while(conn.rs.next()){
                f = true;
            }
            conn.myClose();
            return f;
        } catch (Exception e) {
        }
        return false;
    }

    public boolean lockUser(int id){
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{call sp_LockUser(?)}");
            conn.cstm.setInt(1, id);
            int n =0;
            n = conn.cstm.executeUpdate();
            if(n>0)
                return true;
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean unLockUser(int id){
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{call sp_unLockUser(?)}");
            conn.cstm.setInt(1, id);
            int n =0;
            n = conn.cstm.executeUpdate();
            if(n>0)
                return true;
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }





}
